﻿using Meiam.System.Interfaces.IService;
using Meiam.System.Model.Entity;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Meiam.System.Interfaces.Service
{
    public class DropboxIndexWorkShopService : SqlSugarBase<DropboxIndexWorkShop>, IDropboxIndexWorkShopService
    {
        public DropboxIndexWorkShopService(IConfiguration _configuration)
        {
            base.connectionString = _configuration.GetConnectionString("DB1");
        }

        #region 落盒数量+占比 12H

        public List<DropboxIndexWorkShop> GetWorkshopOneData12H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-12);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (
SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
--  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES INPIECES,
--  pg.SUBPIECES DiffPiece,
   MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
  @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.3-0.4)' THEN pt.BADCOUNT
	ELSE 0
	END) "+"\"A+(0.3-0.4)\","+
    @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.4-0.5)' THEN pt.BADCOUNT
	ELSE 0
	END) "+"\"A+(0.4-0.5)\","+
    @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.5-0.8)' THEN pt.BADCOUNT
	ELSE 0
	END) "+"\"A+(0.5-0.8)\","+
    @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.8-1.1)' THEN pt.BADCOUNT
	ELSE 0
	END) "+"\"A+(0.8-1.1)\","+
    @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(1.1-1.5)' THEN pt.BADCOUNT
	ELSE 0
	END) "+"\"A+(1.1-1.5)\","+
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"," +
 @"MAX(CASE
	WHEN pt.BADTYPE = 'B重片' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"B重片\"" +
@" FROM t_fx_prductgroup@scada2mes pg
LEFT JOIN t_fx_product_type@scada2mes pt
   ON pg.ID = pt.FID
WHERE pg.RECORDDATE BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
AND REGEXP_LIKE(pg.EQUIPMENTNAME,'(^T|A025)')
GROUP BY pg.f1,pg.EQUIPMENTNAME,pg.INPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE SPECIAL,
altb.Curealqty THEMORYPIECES 
FROM CONTAINER c
LEFT JOIN PRODUCT p
  ON c.PRODUCTID = p.PRODUCTID
LEFT JOIN a_lotattributes altb 
	ON c.containerid = altb.containerid
)
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES,
  TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
 "TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
 "TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
 "TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
 @"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
 LEFT JOIN S
 ON DS.DH = S.CONTAINERNAME
GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
  TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES," +
 "TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
 "TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
 "TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
 "TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
 @"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
UNION ALL
SELECT
  '分选机台号',
  '切片理论数',
  '进片数',
  '差异数',
  'A+',
  'A-污片',
  'A-崩边',
  'A线',
  'B硅落',
  'B线',
  'B厚薄',
  'D隐裂',
  'D缺角',
  '未测到',
  'B重片'
FROM DUAL
UNION ALL
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
 @"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
 GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
 TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
 @"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
     FROM DS
     LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
";

            return base.Query(sql);

        }

        public List<DropboxIndexWorkShop> GetWorkshopTwoData12H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-12);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (
SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
--  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES INPIECES,
--  pg.SUBPIECES DiffPiece,
   MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
  @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.3-0.4)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.3-0.4)\"," +
    @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.4-0.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.4-0.5)\"," +
    @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.5-0.8)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.5-0.8)\"," +
    @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.8-1.1)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.8-1.1)\"," +
    @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(1.1-1.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(1.1-1.5)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
 @"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"," +
 @"MAX(CASE
	WHEN pt.BADTYPE = 'B重片' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"B重片\"" +
@" FROM t_fx_prductgroup@scada2mes pg
LEFT JOIN t_fx_product_type@scada2mes pt
   ON pg.ID = pt.FID
WHERE pg.RECORDDATE BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
AND REGEXP_LIKE(pg.EQUIPMENTNAME,'^A') AND NOT REGEXP_LIKE(pg.EQUIPMENTNAME,'A025') 
GROUP BY pg.f1,pg.EQUIPMENTNAME,pg.INPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE SPECIAL,
altb.Curealqty THEMORYPIECES 
FROM CONTAINER c
LEFT JOIN PRODUCT p
  ON c.PRODUCTID = p.PRODUCTID
LEFT JOIN a_lotattributes altb 
	ON c.containerid = altb.containerid
)
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES,
  TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
 "TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
 "TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
 "TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
 @"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
 LEFT JOIN S
 ON DS.DH = S.CONTAINERNAME
GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
  TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES," +
 "TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
 "TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
 "TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
 "TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
 @"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
UNION ALL
SELECT
  '分选机台号',
  '切片理论数',
  '进片数',
  '差异数',
  'A+',
  'A-污片',
  'A-崩边',
  'A线',
  'B硅落',
  'B线',
  'B厚薄',
  'D隐裂',
  'D缺角',
  '未测到',
  'B重片'
FROM DUAL
UNION ALL
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
 @"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
 GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
 TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
 "DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
 @"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
     FROM DS
     LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
";

          
            return base.Query(sql);

        }

        public List<DropboxIndexWorkShop> GetWorkshopThreeData12H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-12);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (
SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
--  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES INPIECES,
--  pg.SUBPIECES DiffPiece,
   MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.3-0.4)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.3-0.4)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.4-0.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.4-0.5)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.5-0.8)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.5-0.8)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.8-1.1)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.8-1.1)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(1.1-1.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(1.1-1.5)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'B重片' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"B重片\"" +
@" FROM t_fx_prductgroup@scada2mes pg
LEFT JOIN t_fx_product_type@scada2mes pt
   ON pg.ID = pt.FID
WHERE pg.RECORDDATE BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
AND REGEXP_LIKE(pg.EQUIPMENTNAME,'^3') 
GROUP BY pg.f1,pg.EQUIPMENTNAME,pg.INPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE SPECIAL,
altb.Curealqty THEMORYPIECES 
FROM CONTAINER c
LEFT JOIN PRODUCT p
  ON c.PRODUCTID = p.PRODUCTID
LEFT JOIN a_lotattributes altb 
	ON c.containerid = altb.containerid
)
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES,
  TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
"TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
"TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
"TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
@"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
 LEFT JOIN S
 ON DS.DH = S.CONTAINERNAME
GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
  TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES," +
"TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
"TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
"TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
"TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
@"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
UNION ALL
SELECT
  '分选机台号',
  '切片理论数',
  '进片数',
  '差异数',
  'A+',
  'A-污片',
  'A-崩边',
  'A线',
  'B硅落',
  'B线',
  'B厚薄',
  'D隐裂',
  'D缺角',
  '未测到',
  'B重片'
FROM DUAL
UNION ALL
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
@"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
 GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
 TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
@"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
     FROM DS
     LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
";

            return base.Query(sql);

        }
       
        #endregion

        #region 落盒数量+占比 2H

        public List<DropboxIndexWorkShop> GetWorkshopOneData2H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (
SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
--  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES INPIECES,
--  pg.SUBPIECES DiffPiece,
   MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
 @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.3-0.4)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.3-0.4)\"," +
   @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.4-0.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.4-0.5)\"," +
   @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.5-0.8)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.5-0.8)\"," +
   @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.8-1.1)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.8-1.1)\"," +
   @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(1.1-1.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(1.1-1.5)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'B重片' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"B重片\"" +
@" FROM t_fx_prductgroup@scada2mes pg
LEFT JOIN t_fx_product_type@scada2mes pt
   ON pg.ID = pt.FID
WHERE pg.RECORDDATE BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
AND REGEXP_LIKE(pg.EQUIPMENTNAME,'(^T|A025)')
GROUP BY pg.f1,pg.EQUIPMENTNAME,pg.INPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE SPECIAL,
altb.Curealqty THEMORYPIECES 
FROM CONTAINER c
LEFT JOIN PRODUCT p
  ON c.PRODUCTID = p.PRODUCTID
LEFT JOIN a_lotattributes altb 
	ON c.containerid = altb.containerid
)
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES,
  TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
"TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
"TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
"TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
@"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
 LEFT JOIN S
 ON DS.DH = S.CONTAINERNAME
GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
  TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES," +
"TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
"TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
"TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
"TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
@"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
UNION ALL
SELECT
  '分选机台号',
  '切片理论数',
  '进片数',
  '差异数',
  'A+',
  'A-污片',
  'A-崩边',
  'A线',
  'B硅落',
  'B线',
  'B厚薄',
  'D隐裂',
  'D缺角',
  '未测到',
  'B重片'
FROM DUAL
UNION ALL
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
@"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
 GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
 TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
@"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
     FROM DS
     LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
";

            return base.Query(sql);

        }

        public List<DropboxIndexWorkShop> GetWorkshopTwoData2H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";
            string sql = @"WITH DS AS (
SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
--  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES INPIECES,
--  pg.SUBPIECES DiffPiece,
   MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.3-0.4)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.3-0.4)\"," +
  @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.4-0.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.4-0.5)\"," +
  @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.5-0.8)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.5-0.8)\"," +
  @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.8-1.1)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.8-1.1)\"," +
  @"MAX(CASE
	WHEN pt.BADTYPE = 'A+(1.1-1.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(1.1-1.5)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'B重片' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"B重片\"" +
@" FROM t_fx_prductgroup@scada2mes pg
LEFT JOIN t_fx_product_type@scada2mes pt
   ON pg.ID = pt.FID
WHERE pg.RECORDDATE BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
AND REGEXP_LIKE(pg.EQUIPMENTNAME,'^A') AND NOT REGEXP_LIKE(pg.EQUIPMENTNAME,'A025') 
GROUP BY pg.f1,pg.EQUIPMENTNAME,pg.INPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE SPECIAL,
altb.Curealqty THEMORYPIECES 
FROM CONTAINER c
LEFT JOIN PRODUCT p
  ON c.PRODUCTID = p.PRODUCTID
LEFT JOIN a_lotattributes altb 
	ON c.containerid = altb.containerid
)
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES,
  TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
"TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
"TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
"TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
@"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
 LEFT JOIN S
 ON DS.DH = S.CONTAINERNAME
GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
  TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES," +
"TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
"TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
"TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
"TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
@"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
UNION ALL
SELECT
  '分选机台号',
  '切片理论数',
  '进片数',
  '差异数',
  'A+',
  'A-污片',
  'A-崩边',
  'A线',
  'B硅落',
  'B线',
  'B厚薄',
  'D隐裂',
  'D缺角',
  '未测到',
  'B重片'
FROM DUAL
UNION ALL
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
@"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
 GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
 TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
@"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
     FROM DS
     LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
";
            return base.Query(sql);

        }

        public List<DropboxIndexWorkShop> GetWorkshopThreeData2H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";
            string sql = @"WITH DS AS (
SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
--  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES INPIECES,
--  pg.SUBPIECES DiffPiece,
   MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.3-0.4)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.3-0.4)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.4-0.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.4-0.5)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.5-0.8)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.5-0.8)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(0.8-1.1)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(0.8-1.1)\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'A+(1.1-1.5)' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"A+(1.1-1.5)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"," +
@"MAX(CASE
	WHEN pt.BADTYPE = 'B重片' THEN pt.BADCOUNT
	ELSE 0
	END) " + "\"B重片\"" +
@" FROM t_fx_prductgroup@scada2mes pg
LEFT JOIN t_fx_product_type@scada2mes pt
   ON pg.ID = pt.FID
WHERE pg.RECORDDATE BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
AND REGEXP_LIKE(pg.EQUIPMENTNAME,'^3') 
GROUP BY pg.f1,pg.EQUIPMENTNAME,pg.INPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE SPECIAL,
altb.Curealqty THEMORYPIECES 
FROM CONTAINER c
LEFT JOIN PRODUCT p
  ON c.PRODUCTID = p.PRODUCTID
LEFT JOIN a_lotattributes altb 
	ON c.containerid = altb.containerid
)
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES,
  TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
"TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
"TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
"TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
@"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
 LEFT JOIN S
 ON DS.DH = S.CONTAINERNAME
GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
  TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
  TO_CHAR(SUM(INPIECES)) INPIECES,
TO_CHAR(SUM(S.THEMORYPIECES)-SUM(INPIECES)) DIFFPIECES," +
"TO_CHAR(SUM(" + "\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\" + \"A+(0.4-0.9)\" + \"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")) \"APlus\"," +
"TO_CHAR(SUM(\"A-污片\")) \"AGRADEWP\"," +
"TO_CHAR(SUM(\"A-崩边\")) \"AGRADEBB\"," +
"TO_CHAR(SUM(\"A-线痕\" + \"A-翘曲\")) AX," +
@"TO_CHAR(SUM(B硅落)) BGL,
  TO_CHAR(SUM(B线痕 + B翘曲)) BX,
  TO_CHAR(SUM(B厚薄 + BTTV)) BHB,
  TO_CHAR(SUM(D隐裂)) DYL,
  TO_CHAR(SUM(D缺角)) DQJ,
  TO_CHAR(SUM(未测到)) WCD,
  TO_CHAR(SUM(B重片)) BCP
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
UNION ALL
SELECT
  '分选机台号',
  '切片理论数',
  '进片数',
  '差异数',
  'A+',
  'A-污片',
  'A-崩边',
  'A线',
  'B硅落',
  'B线',
  'B厚薄',
  'D隐裂',
  'D缺角',
  '未测到',
  'B重片'
FROM DUAL
UNION ALL
SELECT
  *
FROM
(SELECT
  MachineNo,
TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
@"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
FROM DS
LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
 GROUP BY MachineNo
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC)
UNION ALL
SELECT
  NVL(S.SPECIAL, '刀号异常') MachineNo,
 TO_CHAR(SUM(S.THEMORYPIECES)) THEMORYPIECES,
	TO_CHAR(SUM(INPIECES)) INPIECES,
	DECODE(SUM(S.THEMORYPIECES), 0, '0', TO_CHAR(ROUND((SUM(S.THEMORYPIECES)-SUM(INPIECES))/SUM(S.THEMORYPIECES),4)*100, 'fm90.09') || '%') DIFFPIECES," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\"+\"A+(0.3-0.4)\"+\"A+(0.4-0.5)\"+\"A+(0.5-0.8)\"+\"A+(0.8-1.1)\"+\"A+(1.1-1.5)\")/SUM(INPIECES),4)*100, 'fm90.09') || '%') \"A+\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-污片\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-污片\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-崩边\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') \"A-崩边\"," +
"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\") / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') A线," +
@"DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B硅落) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B硅落,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B线痕 + B翘曲) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B线,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B厚薄 + BTTV) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') B厚薄,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D隐裂) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D隐裂,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(D缺角) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') D缺角,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(未测到) / SUM(INPIECES), 4) * 100, 'fm90.09') || '%') 未测到,
  DECODE(SUM(INPIECES), 0, '0', TO_CHAR(ROUND(SUM(B重片)/SUM(INPIECES),4)*100, 'fm90.09') || '%') B重片
     FROM DS
     LEFT JOIN S
  ON DS.DH = S.CONTAINERNAME
GROUP BY S.SPECIAL
";

            return base.Query(sql);

        }
     
        #endregion

        #region 落盒数量

        public List<DropboxIndexWorkShop> GetDropBoxIndexWorkshopOneData()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES InPiece,
  pg.SUBPIECES DiffPiece,
  MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"" +
@" FROM t_fx_prductgroup@scada2mes pg 
 LEFT JOIN t_fx_product_type@scada2mes pt 
   ON pg.ID = pt.FID 
WHERE pg.RECORDDATE >=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND pg.RECORDDATE<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
 AND REGEXP_LIKE(pg.EQUIPMENTNAME, '(^T|A025)') GROUP BY pg.f1, pg.EQUIPMENTNAME, pg.THEMORYPIECES, pg.INPIECES, pg.SUBPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE 规格 
 FROM CONTAINER c 
 LEFT JOIN PRODUCT p 
 ON c.PRODUCTID = p.PRODUCTID 
)
SELECT
  * 
FROM
(SELECT 
  MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
 @"SUM(" + "\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\") \"APlus\"," +
 @"SUM(" + "\"A-污片\") \"AGRADEWP\"," +
 @"SUM(" + "\"A-崩边\") \"AGRADEBB\"," +
 @"SUM(" + "\"A-线痕\"+\"A-翘曲\") AX," +
  @"SUM(B硅落) BGL,
  SUM(B线痕 + B翘曲) BX,
  SUM(B厚薄 + BTTV) BHB,
  SUM(D隐裂) DYL,
  SUM(D缺角) DQJ,
  SUM(未测到) WCD 
 FROM DS 
GROUP BY MachineNo 
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC) 
UNION ALL 
SELECT 
  NVL(S.规格, '刀号异常') MachineNo,
  SUM(TheoryPiece) ,
  SUM(InPiece),
  SUM(DiffPiece)," +
@"SUM(" + "\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\") \"APlus\"," +
@"SUM(" + "\"A-污片\") \"AGRADEWP\"," +
@"SUM(" + "\"A-崩边\") \"AGRADEBB\"," +
@"SUM(" + "\"A-线痕\"+\"A-翘曲\") AX," +
@"SUM(B硅落) BGL,
  SUM(B线痕 + B翘曲) BX,
  SUM(B厚薄 + BTTV) BHB,
  SUM(D隐裂) DYL,
  SUM(D缺角) DQJ,
  SUM(未测到) WCD 
FROM DS 
LEFT JOIN S  
ON DS.DH = S.CONTAINERNAME  
GROUP BY S.规格
";

            return base.Query(sql);
 }

        public List<DropboxIndexWorkShop> GetDropBoxIndexWorkshopTwoData()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES InPiece,
  pg.SUBPIECES DiffPiece,
  MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"" +
@" FROM t_fx_prductgroup@scada2mes pg 
 LEFT JOIN t_fx_product_type@scada2mes pt 
   ON pg.ID = pt.FID 
WHERE pg.RECORDDATE >=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND pg.RECORDDATE<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
 AND REGEXP_LIKE(pg.EQUIPMENTNAME,'^A') AND NOT REGEXP_LIKE(pg.EQUIPMENTNAME,'A025')  GROUP BY pg.f1, pg.EQUIPMENTNAME, pg.THEMORYPIECES, pg.INPIECES, pg.SUBPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE 规格 
 FROM CONTAINER c 
 LEFT JOIN PRODUCT p 
 ON c.PRODUCTID = p.PRODUCTID 
)
SELECT
  * 
FROM
(SELECT 
  MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
 @"SUM(" + "\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\") \"APlus\"," +
 @"SUM(" + "\"A-污片\") \"AGRADEWP\"," +
 @"SUM(" + "\"A-崩边\") \"AGRADEBB\"," +
 @"SUM(" + "\"A-线痕\"+\"A-翘曲\") AX," +
  @"SUM(B硅落) BGL,
  SUM(B线痕 + B翘曲) BX,
  SUM(B厚薄 + BTTV) BHB,
  SUM(D隐裂) DYL,
  SUM(D缺角) DQJ,
  SUM(未测到) WCD 
 FROM DS 
GROUP BY MachineNo 
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC) 
UNION ALL 
SELECT 
  NVL(S.规格, '刀号异常') MachineNo,
  SUM(TheoryPiece) ,
  SUM(InPiece),
  SUM(DiffPiece)," +
@"SUM(" + "\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\") \"APlus\"," +
@"SUM(" + "\"A-污片\") \"AGRADEWP\"," +
@"SUM(" + "\"A-崩边\") \"AGRADEBB\"," +
@"SUM(" + "\"A-线痕\"+\"A-翘曲\") AX," +
@"SUM(B硅落) BGL,
  SUM(B线痕 + B翘曲) BX,
  SUM(B厚薄 + BTTV) BHB,
  SUM(D隐裂) DYL,
  SUM(D缺角) DQJ,
  SUM(未测到) WCD 
FROM DS 
LEFT JOIN S  
ON DS.DH = S.CONTAINERNAME  
GROUP BY S.规格";

            return base.Query(sql);
        }

        public List<DropboxIndexWorkShop> GetDropBoxIndexWorkshopThreeData()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES InPiece,
  pg.SUBPIECES DiffPiece,
  MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
  @"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"" +
@" FROM t_fx_prductgroup@scada2mes pg 
 LEFT JOIN t_fx_product_type@scada2mes pt 
   ON pg.ID = pt.FID 
WHERE pg.RECORDDATE >=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND pg.RECORDDATE<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
 AND REGEXP_LIKE(pg.EQUIPMENTNAME,'^3')  GROUP BY pg.f1, pg.EQUIPMENTNAME, pg.THEMORYPIECES, pg.INPIECES, pg.SUBPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE 规格 
 FROM CONTAINER c 
 LEFT JOIN PRODUCT p 
 ON c.PRODUCTID = p.PRODUCTID 
)
SELECT
  * 
FROM
(SELECT 
  MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
 @"SUM(" + "\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\") \"APlus\"," +
 @"SUM(" + "\"A-污片\") \"AGRADEWP\"," +
 @"SUM(" + "\"A-崩边\") \"AGRADEBB\"," +
 @"SUM(" + "\"A-线痕\"+\"A-翘曲\") AX," +
  @"SUM(B硅落) BGL,
  SUM(B线痕 + B翘曲) BX,
  SUM(B厚薄 + BTTV) BHB,
  SUM(D隐裂) DYL,
  SUM(D缺角) DQJ,
  SUM(未测到) WCD 
 FROM DS 
GROUP BY MachineNo 
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC) 
UNION ALL 
SELECT 
  NVL(S.规格, '刀号异常') MachineNo,
  SUM(TheoryPiece) ,
  SUM(InPiece),
  SUM(DiffPiece)," +
@"SUM(" + "\"A+(0.4-1.1)\"+\"A+(1.1-1.8)\"+\"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\") \"APlus\"," +
@"SUM(" + "\"A-污片\") \"AGRADEWP\"," +
@"SUM(" + "\"A-崩边\") \"AGRADEBB\"," +
@"SUM(" + "\"A-线痕\"+\"A-翘曲\") AX," +
@"SUM(B硅落) BGL,
  SUM(B线痕 + B翘曲) BX,
  SUM(B厚薄 + BTTV) BHB,
  SUM(D隐裂) DYL,
  SUM(D缺角) DQJ,
  SUM(未测到) WCD 
FROM DS 
LEFT JOIN S  
ON DS.DH = S.CONTAINERNAME  
GROUP BY S.规格";

            return base.Query(sql);
        }

        #endregion

        #region 占比
        public List<DropboxIndexWorkShop> GetGetDropBoxIndexWorkshopOneRateData()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES InPiece,
  pg.SUBPIECES DiffPiece,
  MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"" +
@" FROM t_fx_prductgroup@scada2mes pg 
 LEFT JOIN t_fx_product_type@scada2mes pt 
   ON pg.ID = pt.FID 
WHERE pg.RECORDDATE >=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND pg.RECORDDATE<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
 AND REGEXP_LIKE(pg.EQUIPMENTNAME, '(^T|A025)')  GROUP BY pg.f1, pg.EQUIPMENTNAME, pg.THEMORYPIECES, pg.INPIECES, pg.SUBPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE 规格 
 FROM CONTAINER c 
 LEFT JOIN PRODUCT p 
 ON c.PRODUCTID = p.PRODUCTID 
)
SELECT
  * 
FROM
(SELECT 
  MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
 "ROUND(SUM(\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\")/SUM(InPiece),4)*100 || \'%\' \"APlus\"," +
  "TO_CHAR(ROUND(SUM(\"A-污片\")/SUM(InPiece),4)*100,\'fm90.09\') || \'%\' \"AGRADEWP\"," +
  "TO_CHAR(ROUND(SUM(\"A-崩边\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' \"AGRADEBB\"," +
  "TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' AX," +
  "TO_CHAR(ROUND(SUM(B硅落)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BGL," +
  "TO_CHAR(ROUND(SUM(B线痕 + B翘曲)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BX," +
  "TO_CHAR(ROUND(SUM(B厚薄 + BTTV)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BHB," +
  "TO_CHAR(ROUND(SUM(D隐裂)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DYL," +
  "TO_CHAR(ROUND(SUM(D缺角)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DQJ," +
  "TO_CHAR(ROUND(SUM(未测到)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' WCD" +
 @" FROM DS 
GROUP BY MachineNo 
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC) 
UNION ALL 
SELECT 
  NVL(S.规格, '刀号异常') MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
 "ROUND(SUM(\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\")/SUM(InPiece),4)*100 || \'%\' \"APlus\"," +
  "TO_CHAR(ROUND(SUM(\"A-污片\")/SUM(InPiece),4)*100,\'fm90.09\') || \'%\' \"AGRADEWP\"," +
  "TO_CHAR(ROUND(SUM(\"A-崩边\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' \"AGRADEBB\"," +
  "TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' AX," +
  "TO_CHAR(ROUND(SUM(B硅落)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BGL," +
  "TO_CHAR(ROUND(SUM(B线痕 + B翘曲)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BX," +
  "TO_CHAR(ROUND(SUM(B厚薄 + BTTV)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BHB," +
  "TO_CHAR(ROUND(SUM(D隐裂)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DYL," +
  "TO_CHAR(ROUND(SUM(D缺角)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DQJ," +
  "TO_CHAR(ROUND(SUM(未测到)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' WCD" +
@" FROM DS 
LEFT JOIN S  
ON DS.DH = S.CONTAINERNAME  
GROUP BY S.规格";

           

            return base.Query(sql);
        }

        public List<DropboxIndexWorkShop> GetGetDropBoxIndexWorkshopTwoRateData()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"WITH DS AS (SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES InPiece,
  pg.SUBPIECES DiffPiece,
  MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"" +
@" FROM t_fx_prductgroup@scada2mes pg 
 LEFT JOIN t_fx_product_type@scada2mes pt 
   ON pg.ID = pt.FID 
WHERE pg.RECORDDATE >=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND pg.RECORDDATE<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
 AND REGEXP_LIKE(pg.EQUIPMENTNAME,'^A') AND NOT REGEXP_LIKE(pg.EQUIPMENTNAME,'A025')  GROUP BY pg.f1, pg.EQUIPMENTNAME, pg.THEMORYPIECES, pg.INPIECES, pg.SUBPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE 规格 
 FROM CONTAINER c 
 LEFT JOIN PRODUCT p 
 ON c.PRODUCTID = p.PRODUCTID 
)
SELECT
  * 
FROM
(SELECT 
  MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
"ROUND(SUM(\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\")/SUM(InPiece),4)*100 || \'%\' \"APlus\"," +
"TO_CHAR(ROUND(SUM(\"A-污片\")/SUM(InPiece),4)*100,\'fm90.09\') || \'%\' \"AGRADEWP\"," +
"TO_CHAR(ROUND(SUM(\"A-崩边\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' \"AGRADEBB\"," +
"TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' AX," +
"TO_CHAR(ROUND(SUM(B硅落)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BGL," +
"TO_CHAR(ROUND(SUM(B线痕 + B翘曲)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BX," +
"TO_CHAR(ROUND(SUM(B厚薄 + BTTV)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BHB," +
"TO_CHAR(ROUND(SUM(D隐裂)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DYL," +
"TO_CHAR(ROUND(SUM(D缺角)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DQJ," +
"TO_CHAR(ROUND(SUM(未测到)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' WCD" +
@" FROM DS 
GROUP BY MachineNo 
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC) 
UNION ALL 
SELECT 
  NVL(S.规格, '刀号异常') MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
"ROUND(SUM(\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\")/SUM(InPiece),4)*100 || \'%\' \"APlus\"," +
"TO_CHAR(ROUND(SUM(\"A-污片\")/SUM(InPiece),4)*100,\'fm90.09\') || \'%\' \"AGRADEWP\"," +
"TO_CHAR(ROUND(SUM(\"A-崩边\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' \"AGRADEBB\"," +
"TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' AX," +
"TO_CHAR(ROUND(SUM(B硅落)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BGL," +
"TO_CHAR(ROUND(SUM(B线痕 + B翘曲)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BX," +
"TO_CHAR(ROUND(SUM(B厚薄 + BTTV)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BHB," +
"TO_CHAR(ROUND(SUM(D隐裂)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DYL," +
"TO_CHAR(ROUND(SUM(D缺角)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DQJ," +
"TO_CHAR(ROUND(SUM(未测到)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' WCD" +
@" FROM DS 
LEFT JOIN S  
ON DS.DH = S.CONTAINERNAME  
GROUP BY S.规格";

            return base.Query(sql);
        }

        public List<DropboxIndexWorkShop> GetGetDropBoxIndexWorkshopThreeRateData()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-2);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString()+" "+(dtNow.Hour>9?dtNow.Hour.ToString():(string.Format("{0}{1}","0",dtNow.Hour)))+":00:00";

            string sql = @"WITH DS AS (SELECT
  UPPER(pg.f1) DH,
  pg.EQUIPMENTNAME MachineNo,
  pg.THEMORYPIECES TheoryPiece,
  pg.INPIECES InPiece,
  pg.SUBPIECES DiffPiece,
  MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.4-0.9)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.4-0.9)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.9-1.1)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.9-1.1)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(1.1-1.8)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(1.1-1.8)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(13-15线痕)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(13-15线痕)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-污片' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-污片\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-崩边' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-崩边\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A-翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A-翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'A+(0.2-0.4)' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"A+(0.2-0.4)\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B硅落' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B硅落\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B线痕' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B线痕\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B翘曲' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B翘曲\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B厚薄' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B厚薄\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'BTTV' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"BTTV\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B电阻' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B电阻\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'B尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"B尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C崩硅' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C崩硅\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'C尺寸' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"C尺寸\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D穿孔' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D穿孔\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D隐裂' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D隐裂\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = 'D缺角' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"D缺角\"," +
@"MAX(CASE
  WHEN pt.BADTYPE = '未测到' THEN pt.BADCOUNT
  ELSE 0
  END) " + "\"未测到\"" +
@" FROM t_fx_prductgroup@scada2mes pg 
 LEFT JOIN t_fx_product_type@scada2mes pt 
   ON pg.ID = pt.FID 
WHERE pg.RECORDDATE >=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND pg.RECORDDATE<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
 AND REGEXP_LIKE(pg.EQUIPMENTNAME,'^3')  GROUP BY pg.f1, pg.EQUIPMENTNAME, pg.THEMORYPIECES, pg.INPIECES, pg.SUBPIECES
),
S AS(
SELECT
  c.CONTAINERNAME,
  p.CUSIZE 规格 
 FROM CONTAINER c 
 LEFT JOIN PRODUCT p 
 ON c.PRODUCTID = p.PRODUCTID 
)
SELECT
  * 
FROM
(SELECT 
  MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
"ROUND(SUM(\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\")/SUM(InPiece),4)*100 || \'%\' \"APlus\"," +
"TO_CHAR(ROUND(SUM(\"A-污片\")/SUM(InPiece),4)*100,\'fm90.09\') || \'%\' \"AGRADEWP\"," +
"TO_CHAR(ROUND(SUM(\"A-崩边\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' \"AGRADEBB\"," +
"TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' AX," +
"TO_CHAR(ROUND(SUM(B硅落)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BGL," +
"TO_CHAR(ROUND(SUM(B线痕 + B翘曲)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BX," +
"TO_CHAR(ROUND(SUM(B厚薄 + BTTV)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BHB," +
"TO_CHAR(ROUND(SUM(D隐裂)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DYL," +
"TO_CHAR(ROUND(SUM(D缺角)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DQJ," +
"TO_CHAR(ROUND(SUM(未测到)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' WCD" +
@" FROM DS 
GROUP BY MachineNo 
ORDER BY DECODE(SUBSTR(MachineNo, 0, 1), 'T', 1, 'A', 2, '3', 3, '4', 4) ASC,
         SUBSTR(MachineNo, -2, 2) ASC) 
UNION ALL 
SELECT 
  NVL(S.规格, '刀号异常') MachineNo,
  SUM(TheoryPiece) TheoryPiece,
  SUM(InPiece) InPiece,
  SUM(DiffPiece) DiffPiece," +
"ROUND(SUM(\"A+(0.4-1.1)\" + \"A+(1.1-1.8)\" + \"A+(0.2-0.4)\"+\"A+(0.4-0.9)\"+\"A+(0.9-1.1)\")/SUM(InPiece),4)*100 || \'%\' \"APlus\"," +
"TO_CHAR(ROUND(SUM(\"A-污片\")/SUM(InPiece),4)*100,\'fm90.09\') || \'%\' \"AGRADEWP\"," +
"TO_CHAR(ROUND(SUM(\"A-崩边\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' \"AGRADEBB\"," +
"TO_CHAR(ROUND(SUM(\"A-线痕\" + \"A-翘曲\")/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' AX," +
"TO_CHAR(ROUND(SUM(B硅落)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BGL," +
"TO_CHAR(ROUND(SUM(B线痕 + B翘曲)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BX," +
"TO_CHAR(ROUND(SUM(B厚薄 + BTTV)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' BHB," +
"TO_CHAR(ROUND(SUM(D隐裂)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DYL," +
"TO_CHAR(ROUND(SUM(D缺角)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' DQJ," +
"TO_CHAR(ROUND(SUM(未测到)/SUM(InPiece),4)*100, \'fm90.09\') || \'%\' WCD" +
@" FROM DS 
LEFT JOIN S  
ON DS.DH = S.CONTAINERNAME  
GROUP BY S.规格";

            return base.Query(sql);
        }

        #endregion
    }
}
